from openpyxl.reader.excel import load_workbook
from pandas.core.frame import DataFrame
from pandas.core.series import Series
from exsheet import exblock_in_sheet, Coord
from pandas import Series
from openpyxl import Workbook

exblst = exblock_in_sheet(absolute_loc=Coord(2,2))
exblst.read_pattern(r'.\data\请款单模板.xlsx')
oto_loc = {
    'M2':'填单日期',
    'E3':'收款单位', 'M3':'请款部门',
    'E4':'银行账号', 'M4':'开户银行',
    'F5':'金额（大写）',
    'M5':'转款金额', 
    'B7':'用途', 
    'O9':'单据数量'
}
adata = Series(data=['2021-6-1', '朱强', '业务部', '222-2222', '银河银行','壹佰万元整', 1000000, '废钢款', 2],
    index=['填单日期','收款单位','请款部门','银行账号','开户银行','金额（大写）','转款金额','用途','单据数量']
)
exblst.fill_blank(oto_loc, adata)
assert exblst.block is not None
assert exblst.Id == 0
assert exblst.number_of_rows_per_page == 0
assert exblst.start_loc is None
assert exblst.page_order == 0
assert exblst.sequence_number_in_a_page == 0

exblst.Id = 1
exblst.fill_blank(oto_loc, adata)

assert exblst.Id == 1
assert exblst.number_of_rows_per_page == exblst.number_of_blocks_per_page * exblst.block.number_of_rows
assert exblst.start_loc.row == 2
assert exblst.start_loc.col == 2
assert exblst.page_order == 1
assert exblst.sequence_number_in_a_page == 1
assert exblst.block.martrix[2-1][11-1].value == '填单日期：'
assert exblst.block.martrix[2-1][11-1].loc.row == ((2-1) +2)
assert exblst.block.martrix[2-1][11-1].loc.col == ((11-1) +2)
assert exblst.block.heights.iloc[2-1] == 30.0
assert exblst.block.heights.index[2-1]  == ((2-1) + 2)
assert exblst.block.widths.iloc[11-1] == 8.88671875
assert exblst.block.widths.index[11-1] == ((11-1) + 2)
assert exblst.block.martrix[3-1][5-1].value == '朱强'
assert exblst.block.martrix[3-1][5-1].loc.row == ((3-1) +2)
assert exblst.block.martrix[3-1][5-1].loc.col == ((5-1) +2)



adata = Series(data=['2021-6-1', '朱自强', '业务部', '222-2222', '银河银行',None, 1000000, '废钢款', 2],
    index=['填单日期','收款单位','请款部门','银行账号','开户银行','金额（大写）','转款金额','用途','单据数量']
)

exblst.Id = 6
exblst.fill_blank(oto_loc,adata)
assert exblst.page_order == 6
assert exblst.sequence_number_in_a_page == 1
assert exblst.block.martrix[2-1][11-1].value == '填单日期：'
assert exblst.block.martrix[2-1][11-1].loc.row == ((2-1) +2 + 5*12)
assert exblst.block.martrix[2-1][11-1].loc.col == ((11-1) +2 + 0)
assert exblst.block.heights.iloc[2-1] == 30.0
assert exblst.block.heights.index[2-1]  == ((2-1) + 2 + 5*12)
assert exblst.block.widths.iloc[11-1] == 8.88671875
assert exblst.block.widths.index[11-1] == ((11-1) + 2 + 0)
assert exblst.block.martrix[3-1][5-1].value == '朱自强'
assert exblst.block.martrix[3-1][5-1].loc.row == ((3-1) +2 + 5*12)
assert exblst.block.martrix[3-1][5-1].loc.col == ((5-1) +2 + 0)

oto_loc = {
    'B2': '供货人',
    'F2': '入库日期'
}
adata = Series(['2021-6-26', '朱强'], index=['入库日期','供货人'])
mtm_loc = {
    '4:11': '行范围',
    'A': '货物名称',
    'C': '车号',
    'D': '单位',
    'E': '数量',
}
datas = DataFrame(data={
                        '车号': ['浙A2958', '赣B7955', '京C2316', '闵D1456'],
                        '货物名称': ['废钢1', '废钢2', '废钢3', '废钢4'],
                        '单位': ['吨', '吨', '吨', '吨'],
                        '数量': [15, 12, 18, 22]}
                        )
blst = exblock_in_sheet(absolute_loc=Coord(2,3), number_of_blocks_per_page=3)
blst.read_pattern(r'.\data\出入库单模板.xlsx')
blst.fill_blank(oto_loc, adata, mtm_loc, datas, receipt='仓库单')
assert blst.block is not None
assert blst.block.martrix[2-1][2-1].value == '朱强'
assert blst.block.martrix[5-1][1-1].value == '废钢2'
assert blst.block.martrix[6-1][3-1].value == '京C2316'
assert blst.block.martrix.shape[0] == 13
assert blst.block.martrix.shape[1] == 6
assert blst.block.merges[0] == 'A1:F1'

adata = Series(['2021年6月26日', '朱自强'], index=['入库日期','供货人'])
# mtm_loc = {
#     '4:11': '行范围',
#     'A': '货物名称',
#     'C': '车号',
#     'E': '数量',
# }
# datas = DataFrame(data={
#                         '车号': ['浙A2958', '赣B7955', '津C2316'],
#                         '货物名称': ['废钢1', '废钢2', '废钢3'],
#                         '单位': ['吨', '吨', '吨'],
#                         '数量': [15, 12, 18]}
#                         )
wb = Workbook()
ws = wb.active
blst.Id = 6
blst.fill_blank(oto_loc, adata, mtm_loc, datas, receipt='仓库单')
assert blst.block is not None
assert blst.block.martrix[2-1][2-1].value == '朱自强'
assert blst.block.martrix[5-1][1-1].value == '废钢2'
# assert blst.block.martrix[6-1][3-1].value == '津C2316'  
assert blst.block.martrix[2-1][2-1].loc.row == (2 + 13*5 + 2 - 1)
assert blst.block.martrix[6-1][3-1].loc.row == (2 + 13*5 + 6 - 1)
assert blst.start_loc.row == blst.absolute_loc.row + (13*5)
assert blst.start_loc.col == blst.absolute_loc.col
assert blst._convert_merged_range('A1:B2',3,3) == 'D4:E5'
assert blst.block.merges[0] == 'C67:H67'
blst.put_it_in_sheet(ws)

adata = Series(['2021年6月29日', '李强'], index=['入库日期','供货人'])
datas = DataFrame(data={
                        '车号': ['浙A2958', '赣B7955', '津C2316', ],
                        '货物名称': ['废钢1', '废钢2', '废钢3', ],
                        '单位': ['吨', '吨', '吨'],
                        '数量': [15, 12, 28, ]}
                        )

blst.Id = 7
blst.fill_blank(oto_loc, adata, mtm_loc, datas, receipt='仓库单')
blst.put_it_in_sheet(ws)
# blst.Id = 7
# blst.fill_blank(oto_loc, adata, mtm_loc, datas, model='仓库单')

# blst.put_it_in_sheet(ws)
wb.save('test.xlsx')

